﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 人员管理
{
    
    public class PersonManager
    {

        private static readonly string constr = "Data Source=.;Initial Catalog=test;User Id=sa;Password=root123;";

        /// <summary>
        /// 删除人员
        /// </summary>
        /// <param name="selId"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        internal bool DeletePerson(long selId)
        {
            bool b = false;

            SqlConnection sqlConnection = new SqlConnection(constr);

            using (sqlConnection)
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " select id,name,age,sex,addr from person order by id asc ";
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                sqlDataAdapter.SelectCommand = sqlCommand;

                // 创建一个更新命令  
                sqlDataAdapter.DeleteCommand =
                    new SqlCommand("Delete from person WHERE id = @id", sqlConnection);

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                sqlDataAdapter.DeleteCommand.Parameters.Add(p1);

                DataTable dt = new DataTable("person");

                sqlDataAdapter.Fill(dt);

                foreach (DataRow drow in dt.Rows)
                {
                    long id = (long)drow[0];
                    if (id == selId)
                    {
                        drow.Delete();
                    }
                }
                sqlDataAdapter.Update(dt);

                b = true;
            }

            return b;
        }

        /// <summary>
        /// 编辑人员
        /// </summary>
        /// <param name="selId"></param>
        /// <param name="name"></param>
        /// <param name="age"></param>
        /// <param name="sex"></param>
        /// <param name="addr"></param>
        /// <returns></returns>
        internal bool EditPerson(long selId, string name, string age, string sex, string addr)
        {
            bool b = false;
            SqlConnection sqlConnection = new SqlConnection(constr);
            using (sqlConnection)
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " select id,name,age,sex,addr from person order by id asc ";
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                sqlDataAdapter.SelectCommand = sqlCommand;

                // 创建一个更新命令  
                sqlDataAdapter.UpdateCommand =
                    new SqlCommand("UPDATE person SET name = @name , sex = @sex ,addr = @addr,age = @age WHERE id = @id", sqlConnection);

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                SqlParameter p2 = new SqlParameter("@name", SqlDbType.VarChar, 10, "name");
                SqlParameter p3 = new SqlParameter("@sex", SqlDbType.VarChar, 10, "sex");
                SqlParameter p4 = new SqlParameter("@addr", SqlDbType.VarChar, 10, "addr");
                SqlParameter p5 = new SqlParameter("@age", SqlDbType.Int, 10, "age");
                sqlDataAdapter.UpdateCommand.Parameters.Add(p1);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p2);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p3);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p4);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p5);

                DataTable dt = new DataTable("person");
                sqlDataAdapter.Fill(dt);

                foreach (DataRow drow in dt.Rows)
                {
                    long id = (long)drow[0];
                    if (id == selId)
                    {
                        drow["name"] = name;
                        drow["sex"] = sex;
                        drow["addr"] = addr;
                        drow["age"] = age;
                    }
                }
                sqlDataAdapter.Update(dt);

                b = true;
            }

            return b;
        }

        /// <summary>
        /// 插入人员
        /// </summary>
        /// <param name="name"></param>
        /// <param name="age"></param>
        /// <param name="sex"></param>
        /// <param name="addr"></param>
        /// <returns></returns>
        internal bool InsertPerson(string name, string age, string sex, string addr)
        {
            bool b = false;
            SqlConnection sqlConnection = new SqlConnection(constr);
            using (sqlConnection)
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " select id,name,age,sex,addr from person order by id asc ";
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                sqlDataAdapter.SelectCommand = sqlCommand;

                sql = "INSERT INTO person (name,age,sex,addr) values(@name,@age,@sex,@addr)";
                SqlCommand sqlCommand1 = new SqlCommand(sql, sqlConnection);
                sqlDataAdapter.InsertCommand = sqlCommand1;

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                SqlParameter p2 = new SqlParameter("@name", SqlDbType.VarChar, 10, "name");
                SqlParameter p3 = new SqlParameter("@sex", SqlDbType.VarChar, 10, "sex");
                SqlParameter p4 = new SqlParameter("@addr", SqlDbType.VarChar, 10, "addr");
                SqlParameter p5 = new SqlParameter("@age", SqlDbType.Int, 10, "age");
                sqlDataAdapter.InsertCommand.Parameters.Add(p1);
                sqlDataAdapter.InsertCommand.Parameters.Add(p2);
                sqlDataAdapter.InsertCommand.Parameters.Add(p3);
                sqlDataAdapter.InsertCommand.Parameters.Add(p4);
                sqlDataAdapter.InsertCommand.Parameters.Add(p5);

                // DataTable 是一个内存数据表，对应数据库中的一张表。
                // DataSet 是一个数据集，可以包含多个DataTable。
                DataTable dt = new DataTable("Person");
                sqlDataAdapter.Fill(dt);

                // 添加
                DataRow dr = dt.NewRow();
                dr["name"] = name;
                dr["age"] = age;
                dr["sex"] = sex;
                dr["addr"] = addr;
                dt.Rows.Add(dr);

                sqlDataAdapter.Update(dt);

                b = true;
            }
            return b;
        }

        internal DataTable SelectAll()
        {
            string sql = " select id,name,age,sex,addr from person order by id asc ";
            SqlConnection conn = new SqlConnection(constr);
            SqlCommand sqlCommand = new SqlCommand(sql, conn);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            DataTable dt = new DataTable("person");
            sqlDataAdapter.Fill(dt);
            return dt;
        }
    }
}
